﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

using System.Text;
using Portal.Environment;
using Portal.Language;
using Portal.Interface;


namespace Portal.CDSP
{
    public class VoteBLL
    {


        public VoteCls CreateModel(string ActiveLanguage, string VoteId)
        {
            string Query = "select * from TableVote where VoteId=@VoteId";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@VoteId", SqlDbType.VarChar);
            sqlCommand.Parameters["@VoteId"].Value = VoteId;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            if (dtResult.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                VoteCls VoteCls = new VoteCls();
                VoteCls.VoteId = (string)dtResult.Rows[0]["VoteId"];
                VoteCls.Subject = "";
                try
                {
                    VoteCls.Subject = (string)dtResult.Rows[0][ActiveLanguage + "Subject"];
                }
                catch { }
                try
                {
                    VoteCls.Owner = (string)dtResult.Rows[0]["Owner"];
                }
                catch { }
                return VoteCls;
            }
        }


        public string GetInfo(string ActiveLanguage, string VoteId, ref VoteItemCls[] VoteItems)
        {
            string Query = "select * from TableVote where VoteId='" + VoteId + "';" +
                           "select * from TableVoteItem where fk_Vote_Id='" + VoteId + "' order by [IndexOrder]";
            SqlConnection sqlConnection = new SqlConnection(Environments.PublicConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(ds);
            VoteItems = new VoteItemCls[ds.Tables[1].Rows.Count];
            for (int iIndex = 0; iIndex < VoteItems.Length; iIndex++)
            {
                VoteItems[iIndex] = new VoteItemCls();
                VoteItems[iIndex].VoteItemId = (string)ds.Tables[1].Rows[iIndex]["VoteItemId"];
                VoteItems[iIndex].VoteItemName = "";
                try
                {
                    VoteItems[iIndex].VoteItemName = (string)ds.Tables[1].Rows[iIndex][ActiveLanguage + "VoteItemName"];
                    VoteItems[iIndex].Total = (int)ds.Tables[1].Rows[iIndex]["TotalVote"];
                }
                catch { }
            }

            return (string)ds.Tables[0].Rows[0][ActiveLanguage + "Subject"];
        }

        public DataTable ReadingVoteItems(string VoteId)
        {
            string Query = "select * from TableVoteItem where fk_Vote_Id='" + VoteId + "' order by [IndexOrder]";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }


        public int GetTotal(string VoteId)
        {
            int Total = 0;
            string Query = "select  sum(TotalVote)as Total from TableVoteItem where fk_Vote_Id ='" + VoteId + "' ";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            Total = (int)sqlCommand.ExecuteScalar();
            return Total;
        }

        public DataTable Reading()
        {
            string Query = "select * from TableVote";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingForService()
        {
            string Query = "select Id=VoteId,Subject=[" + Environments.ActiveLanguage + "Subject] from TableVote order by Subject";
            SqlConnection sqlConnection = new SqlConnection(Environments.PublicConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public void IncVoteItem(SqlTransaction sqlTransaction, string VoteItemId)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environments.PublicConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string GroupNewsId = System.Guid.NewGuid().ToString();
            string Query = " Update TableVoteItem set TotalVote=TotalVote+1 where VoteItemId=@VoteItemId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@VoteItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@VoteItemId"].Value = VoteItemId;

            try
            {
                sqlCommand.ExecuteNonQuery();
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }

        public void DeleteVote(SqlTransaction sqlTransaction, string VoteId)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }
            //Environments.DeletePermission(sqlTransaction, VoteId);
            string Query =
                    " Delete from TableVoteItem where fk_Vote_Id=@VoteId;" +
                    " Delete from TableVote where VoteId=@VoteId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@VoteId", SqlDbType.VarChar);
            sqlCommand.Parameters["@VoteId"].Value = VoteId;
            try
            {
                sqlCommand.ExecuteNonQuery();

                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }

        }

        public void DeleteFAQ(SqlTransaction sqlTransaction, string AskID)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            // Environment.Permission.DeletePermission(sqlTransaction, AskID);
            string Query = " Delete from TableFAQ where AskID=@AskID";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@AskID", SqlDbType.VarChar);
            sqlCommand.Parameters["@AskID"].Value = AskID;

            try
            {
                sqlCommand.ExecuteNonQuery();

                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }


        public string AddVote(SqlTransaction sqlTransaction, string ActiveLanguage, string Subject, VoteItemCls[] VoteItems)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string VoteId = System.Guid.NewGuid().ToString();
            string Query = "Insert into TableVote(VoteId,[" + ActiveLanguage + "Subject]) values (@VoteId,@Subject)";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@VoteId", SqlDbType.VarChar);
            sqlCommand.Parameters["@VoteId"].Value = VoteId;

            sqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Subject"].Value = Subject;

            try
            {
                sqlCommand.ExecuteNonQuery();
                //doan nay call webservice
                DataSet ds = new DataSet("xml");

                if (Portal.Environment.Environments.UseFrontEndDatabase)
                {
                    ds.Tables.Add("Votes");
                    ds.Tables.Add("VotesItems");

                    ds.Tables[0].Columns.Add("ActiveLanguage");
                    ds.Tables[0].Columns.Add("VoteId");
                    ds.Tables[0].Columns.Add("Subject");
                    ds.Tables[0].Columns.Add("Owner");

                    ds.Tables[1].Columns.Add("VoteItemId");
                    ds.Tables[1].Columns.Add("VoteItemName");
                    ds.Tables[1].Columns.Add("IndexOrder");

                    string owner = Environments.CurrentLoginUser.LoginName;
                    ds.Tables[0].Rows.Add(new object[]{
                        ActiveLanguage,VoteId,Subject,owner
                    });
                }
                for (int iIndex = 0; iIndex < VoteItems.Length; iIndex++)
                {
                    if (VoteItems[iIndex].VoteItemName != null)
                    {
                        if (VoteItems[iIndex].VoteItemName.Trim().Length > 0)
                        {
                            Query = "Insert into TableVoteItem(VoteItemId,VoteItemName,TotalVote,IndexOrder,fk_Vote_Id) values (@VoteItemId,@VoteItemName,0,@IndexOrder,@fk_Vote_Id)";
                            sqlCommand = sqlConnection.CreateCommand();
                            sqlCommand.CommandText = Query;
                            sqlCommand.Transaction = sqlTransaction;
                            sqlCommand.Parameters.Clear();
                            string VoteItemId = System.Guid.NewGuid().ToString();
                            sqlCommand.Parameters.Add("@VoteItemId", SqlDbType.VarChar);
                            sqlCommand.Parameters["@VoteItemId"].Value = VoteItemId;

                            sqlCommand.Parameters.Add("@VoteItemName", SqlDbType.NVarChar);
                            sqlCommand.Parameters["@VoteItemName"].Value = VoteItems[iIndex].VoteItemName;

                            sqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
                            sqlCommand.Parameters["@IndexOrder"].Value = VoteItems[iIndex].IndexOrder;

                            sqlCommand.Parameters.Add("@fk_Vote_Id", SqlDbType.VarChar);
                            sqlCommand.Parameters["@fk_Vote_Id"].Value = VoteId;

                            sqlCommand.ExecuteNonQuery();

                            if (Portal.Environment.Environments.UseFrontEndDatabase)
                            {
                                ds.Tables[1].Rows.Add(new object[]{
                                       VoteItemId,VoteItems[iIndex].VoteItemName,VoteItems[iIndex].IndexOrder.ToString()
                                   });
                            }
                        }
                    }
                }

                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
            return VoteId;
        }

        public void AppendVote(
           SqlTransaction sqlTransaction,
           string ActiveLanguage,
           string VoteId,
           string VoteName,
           VoteItemCls[] VoteItems)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.Transaction = sqlTransaction;
            string Query = "Update TableVote set [" + ActiveLanguage + "Subject]=@Subject where VoteId=@VoteId";
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@VoteId", SqlDbType.VarChar);
            sqlCommand.Parameters["@VoteId"].Value = VoteId;

            sqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Subject"].Value = VoteName;

            try
            {
                sqlCommand.ExecuteNonQuery();
                //doan nay call webservice
                DataSet ds = new DataSet("xml");

                if (Portal.Environment.Environments.UseFrontEndDatabase)
                {
                    ds.Tables.Add("Vote");
                    ds.Tables.Add("VoteItems");

                    ds.Tables[0].Columns.Add("ActiveLanguage");
                    ds.Tables[0].Columns.Add("VoteId");
                    ds.Tables[0].Columns.Add("Subject");

                    ds.Tables[1].Columns.Add("VoteItemId");
                    ds.Tables[1].Columns.Add("VoteItemName");
                    ds.Tables[1].Columns.Add("IndexOrder");


                    ds.Tables[0].Rows.Add(new object[]{
                        ActiveLanguage,VoteId,VoteName
                    });
                }
                for (int iIndex = 0; iIndex < VoteItems.Length; iIndex++)
                {
                    if (VoteItems[iIndex].VoteItemName != null)
                    {
                        if (VoteItems[iIndex].VoteItemName.Trim().Length > 0)
                        {
                            Query = "Insert into TableVoteItem(VoteItemId," + ActiveLanguage + "VoteItemName],TotalVote,IndexOrder,fk_Vote_Id) values (@VoteItemId,@VoteItemName,0,@IndexOrder,@fk_Vote_Id)";
                            sqlCommand = sqlConnection.CreateCommand();
                            sqlCommand.CommandText = Query;
                            sqlCommand.Transaction = sqlTransaction;
                            sqlCommand.Parameters.Clear();
                            sqlCommand.Parameters.Add("@VoteItemId", SqlDbType.VarChar);
                            sqlCommand.Parameters["@VoteItemId"].Value = System.Guid.NewGuid().ToString();

                            sqlCommand.Parameters.Add("@VoteItemName", SqlDbType.NVarChar);
                            sqlCommand.Parameters["@VoteItemName"].Value = VoteItems[iIndex].VoteItemName;

                            sqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
                            sqlCommand.Parameters["@IndexOrder"].Value = VoteItems[iIndex].IndexOrder;

                            sqlCommand.Parameters.Add("@fk_Vote_Id", SqlDbType.VarChar);
                            sqlCommand.Parameters["@fk_Vote_Id"].Value = VoteId;

                            sqlCommand.ExecuteNonQuery();


                            if (Portal.Environment.Environments.UseFrontEndDatabase)
                            {
                                ds.Tables[1].Rows.Add(new object[]{
                                       VoteItems[iIndex].VoteItemId,VoteItems[iIndex].VoteItemName,VoteItems[iIndex].IndexOrder.ToString()
                                   });
                            }
                        }
                    }
                }


                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }

        public void UpdateVote(SqlTransaction sqlTransaction, string ActiveLanguage, string VoteId, string Subject, VoteItemCls[] VoteItems)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string Query = "Update TableVote set [" + ActiveLanguage + "Subject]=@Subject where VoteId=@VoteId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@VoteId", SqlDbType.VarChar);
            sqlCommand.Parameters["@VoteId"].Value = VoteId;

            sqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Subject"].Value = Subject;

            try
            {
                sqlCommand.ExecuteNonQuery();

                //doan nay call webservice
                DataSet ds = new DataSet("xml");

                //if (Portal.Environment.Environments.UseFrontEndDatabase)
                //{
                ds.Tables.Add("Votes");
                ds.Tables.Add("VotesItems");

                ds.Tables[0].Columns.Add("ActiveLanguage");
                ds.Tables[0].Columns.Add("VoteId");
                ds.Tables[0].Columns.Add("Subject");


                ds.Tables[1].Columns.Add("VoteItemId");
                ds.Tables[1].Columns.Add("VoteItemName");
                ds.Tables[1].Columns.Add("IndexOrder");


                ds.Tables[0].Rows.Add(new object[]{
                        ActiveLanguage,VoteId,Subject
                    });
                //}


                sqlCommand.Parameters.Clear();


                string VoteIds = "(";

                Query = "Delete TableVoteItem Where fk_Vote_Id = '" + VoteId + "'";
                sqlCommand.CommandText = Query;
                sqlCommand.ExecuteNonQuery();

                for (int iIndex = 0; iIndex < VoteItems.Length; iIndex++)
                {
                    if (VoteItems[iIndex].VoteItemId == null) VoteItems[iIndex].VoteItemId = System.Guid.NewGuid().ToString();
                    VoteIds = VoteIds + "'" + VoteItems[iIndex].VoteItemId + "'";
                    if (iIndex < VoteItems.Length - 1)
                    {
                        VoteIds += ",";
                    }

                    if (VoteItems[iIndex].VoteItemName != null)
                    {
                        if (VoteItems[iIndex].VoteItemName.Trim().Length > 0)
                        {
                            Query = "Insert into TableVoteItem(VoteItemId,[" + ActiveLanguage + "VoteItemName],TotalVote,IndexOrder,fk_Vote_Id) values (@VoteItemId,@VoteItemName,0,@IndexOrder,@fk_Vote_Id)";
                            sqlCommand = sqlConnection.CreateCommand();
                            sqlCommand.CommandText = Query;
                            sqlCommand.Transaction = sqlTransaction;
                            sqlCommand.Parameters.Clear();
                            sqlCommand.Parameters.Add("@VoteItemId", SqlDbType.VarChar);
                            //sqlCommand.Parameters["@VoteItemId"].Value = VoteItems[iIndex].VoteItemId;
                            sqlCommand.Parameters["@VoteItemId"].Value = System.Guid.NewGuid().ToString();

                            sqlCommand.Parameters.Add("@VoteItemName", SqlDbType.NVarChar);
                            sqlCommand.Parameters["@VoteItemName"].Value = VoteItems[iIndex].VoteItemName;

                            sqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
                            sqlCommand.Parameters["@IndexOrder"].Value = VoteItems[iIndex].IndexOrder;

                            sqlCommand.Parameters.Add("@fk_Vote_Id", SqlDbType.VarChar);
                            sqlCommand.Parameters["@fk_Vote_Id"].Value = VoteId;
                            try
                            {
                                sqlCommand.ExecuteNonQuery();
                            }
                            catch { }

                            //xong roi lai update
                            //Query = " Update TableVoteItem set [" + ActiveLanguage + "VoteItemName]=@VoteItemname,IndexOrder=@IndexOrder where VoteItemId=@VoteItemId";
                            //sqlCommand = sqlConnection.CreateCommand();
                            //sqlCommand.CommandText = Query;
                            //sqlCommand.Transaction = sqlTransaction;
                            //sqlCommand.Parameters.Clear();
                            //sqlCommand.Parameters.Add("@VoteItemId", SqlDbType.VarChar);
                            //sqlCommand.Parameters["@VoteItemId"].Value = VoteItems[iIndex].VoteItemId;

                            //sqlCommand.Parameters.Add("@VoteItemName", SqlDbType.NVarChar);
                            //sqlCommand.Parameters["@VoteItemName"].Value = VoteItems[iIndex].VoteItemName;

                            //sqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
                            //sqlCommand.Parameters["@IndexOrder"].Value = VoteItems[iIndex].IndexOrder;

                            //sqlCommand.ExecuteNonQuery();


                            //if (Portal.Environment.Environments.UseFrontEndDatabase)
                            //{
                            ds.Tables[1].Rows.Add(new object[]{
                                       VoteItems[iIndex].VoteItemId,VoteItems[iIndex].VoteItemName,VoteItems[iIndex].IndexOrder.ToString()
                                   });
                            //}
                        }
                    }
                }

                VoteIds += ")";

                //if (VoteIds.Trim().Equals("()") == false)
                //{
                //    sqlCommand.Parameters.Clear();
                //    sqlCommand.CommandText = "Delete TableVoteItem where fk_vote_id = @VoteId and VoteItemId not in " + VoteIds;
                //    sqlCommand.Parameters.Add("@VoteId",SqlDbType.VarChar);
                //    sqlCommand.Parameters["@VoteId"].Value = VoteId;
                //    sqlCommand.ExecuteNonQuery();
                //}


                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }


        }
    }
}
